package com.zhaoyang.dao;

import com.zhaoyang.model.Page;
import com.zhaoyang.model.SelectedCourse;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 赵扬
 * @time 2020/4/27 19:53
 */
public class SelectedCourseDao extends BaseDao {

    /**
     * 检查学生是否已经选择该门课程
     *
     * @param studentId
     * @param courseId
     * @return 是否已经选择该门课程
     */
    public boolean isSelected(int studentId, int courseId) {
        String sql = "select * from s_selected_course" +
                " where student_id = ? and course_id = ?";
        List<SelectedCourse> res = template.query(sql, new BeanPropertyRowMapper<SelectedCourse>(SelectedCourse.class),
                studentId, courseId);
        if (0 == res.size()) {
            return false;
        }
        return true;
    }

    /**
     * 添加选课信息
     *
     * @param selectedCourse 选课信息实体
     * @return 是否添加成功
     */
    public boolean addSelectedCourse(SelectedCourse selectedCourse) {
        String sql = "insert into s_selected_course values(null,?,?)";
        int cnt = template.update(sql, selectedCourse.getStudentId(), selectedCourse.getCourseId());
        return cnt == 1;
    }

    /**
     * 删除所选课程
     *
     * @param id 选课信息编号
     * @return 是否删除
     */
    public boolean deleteSelectedCourse(int id) {
        String sql = "delete from s_selected_course where id = ?";
        int cnt = template.update(sql, id);
        return cnt == 1;
    }

    /**
     * 获取一条选课数据
     *
     * @param id 选课信息编号
     * @return 选课信息
     */
    public SelectedCourse getSelectedCourse(int id) {
        String sql = "select * from s_selected_course where id = ?";
        List<SelectedCourse> courses = template.query(sql,
                new BeanPropertyRowMapper<SelectedCourse>(SelectedCourse.class), id);
        if (0 == courses.size()) {
            return null;
        }
        return courses.get(0);
    }

    /**
     * 根据选课信息列表
     *
     * @param selectedCourse 选课信息
     * @param page           分页信息
     * @return 选课信息列表
     */
    public List<SelectedCourse> getSelectedCourseList(SelectedCourse selectedCourse, Page page) {
        List<SelectedCourse> ret = new ArrayList<SelectedCourse>();
        String sql = "select * from s_selected_course ";
        if (selectedCourse.getStudentId() != 0) {
            sql += " and student_id = " + selectedCourse.getStudentId();
        }
        if (selectedCourse.getCourseId() != 0) {
            sql += " and course_id = " + selectedCourse.getCourseId();
        }
        sql += " limit " + page.getStart() + "," + page.getPageSize();
        sql = sql.replaceFirst("and", "where");
        ResultSet resultSet = query(sql);
        try {
            while (resultSet.next()) {
                SelectedCourse cl = new SelectedCourse();
                cl.setId(resultSet.getInt("id"));
                cl.setCourseId(resultSet.getInt("course_id"));
                cl.setStudentId(resultSet.getInt("student_id"));
                ret.add(cl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * @param selectedCourse
     * @return
     */
    public int getSelectedCourseListTotal(SelectedCourse selectedCourse) {
        int total = 0;
        String sql = "select count(*)as total from s_selected_course ";
        if (selectedCourse.getStudentId() != 0) {
            sql += " and student_id = " + selectedCourse.getStudentId();
        }
        if (selectedCourse.getCourseId() != 0) {
            sql += " and course_id = " + selectedCourse.getCourseId();
        }
        ResultSet resultSet = query(sql.replaceFirst("and", "where"));
        try {
            while (resultSet.next()) {
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
}
